﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;

namespace SqlServer.SqlHelper
{
    public class SqlHelper
    {
        /// <summary>
        /// ConnectionString connection to SQL Server 2008
        /// </summary>
        /// <returns>ConnectionString</returns>
        /// 

        public SqlConnection GetConnectionString()
        {
            string str = WebConfigurationManager.ConnectionStrings["conn"].ConnectionString;
            SqlConnection conn = new SqlConnection(str);
            if (conn != null)
                conn.Close();
            conn.Open();
            return conn;
        }

        /// <summary>
        /// Execute a store procedure return a instance of SqlDataReader
        /// </summary>
        /// <param name="procName">Store Procedure Name</param>
        /// <param name="procParams">Param List</param>
        /// <returns>A instance of SqlDataReader contain result of stored procedure</returns>
        public SqlDataReader ExecuteReader(string procName, params SqlParameter[] procParams)
        {

            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {

                cmd = new SqlCommand();
                cmd.CommandTimeout = 9999;
                cmd.Connection = GetConnectionString();
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (procParams != null)
                {
                    for (int i = 0; i < procParams.Length; i++)
                    {
                        cmd.Parameters.Add(procParams[i]);
                    }
                }
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                throw;
            }
            return reader;
        }
        /// <summary>
        /// Execute a store procedure return all records  to influence
        /// </summary>
        /// <param name="procName">Stored procedure name</param>
        /// <param name="procParams">Param List</param>
        /// <returns>return all records  to be affected after executing the stored procedure</returns>
        public int ExecuteNonQuery(string procName, params SqlParameter[] procParams)
        {
            SqlCommand cmd = null;
            int affectedRows = 0;
            try
            {

                cmd = new SqlCommand();
                cmd.CommandTimeout = 9999;
                cmd.Connection = GetConnectionString();
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (procParams != null)
                {
                    for (int i = 0; i < procParams.Length; i++)
                    {
                        cmd.Parameters.Add(procParams[i]);
                    }
                }
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (NullReferenceException e)
            {
                Console.WriteLine("Loi nay la  : " + e.Message.ToString());
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
            return affectedRows;
        }

        /// <summary>
        /// Execute a store procedure return first row and first column of all records
        /// </summary>
        /// <param name="procName">Stored procedure name</param>
        /// <param name="procParams">Param List</param>
        /// <returns>return first row and first column of a stored procedure is boxing</returns>
        public object ExecuteScalar(string procName, params SqlParameter[] procParams)
        {
            SqlCommand cmd = null;
            object value;
            try
            {
                cmd = new SqlCommand();
                cmd.CommandTimeout = 9999;
                cmd.Connection = GetConnectionString();
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                if (procParams != null)
                {
                    for (int i = 0; i < procParams.Length; i++)
                    {
                        cmd.Parameters.Add(procParams[i]);
                    }
                }
                value = cmd.ExecuteScalar();
            }
            catch
            {
                throw;
            }
            return value;
        }
        /// <summary>
        /// Execute procedure with DataSet type
        /// </summary>
        /// <param name="procName">Stored procedure name</param>
        /// <param name="procParams">Param List</param>
        /// <returns>A instance of DataSet</returns>
        public DataTable ExecuteDataTable(string procName, params SqlParameter[] procParams)
        {
            DataTable table = new DataTable();
            SqlDataAdapter adapter = null;
            DataSet ds = new DataSet();
            SqlCommand cmd = null;
            try
            {

                cmd = new SqlCommand(procName, GetConnectionString());
                cmd.CommandTimeout = 9999;
                cmd.CommandType = CommandType.StoredProcedure;
                if (procParams != null)
                {
                    for (int i = 0; i < procParams.Length; i++)
                    {
                        cmd.Parameters.Add(procParams[i]);
                    }
                }
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
                table = ds.Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                if (adapter != null)
                {
                    adapter.Dispose();
                }
            }
            return table;
        }
        /// <summary>
        /// Create a new param with SqlParameter type 
        /// </summary>
        /// <param name="paramName">Param Name</param>
        /// <param name="value">Param Value</param>
        /// <returns>A instance of SqlParameter</returns>
        public SqlParameter CreateParameter(string paramName, object value)
        {
            SqlParameter param = new SqlParameter(paramName, value);
            return param;
        }
    }
}
